IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdDetail_Update'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdDetail_Update
GO

CREATE PROC dbo.pInProdDetail_Update
	@productId INT,
	@amount INT,
	@inputPrice INT,
	@inPaperDetailId INT
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/


/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo        INT,
    @strErrMsg       NVARCHAR(400),
    @intReturn       INT,
    @oldAmount		 INT,
    @oldRemainAmount INT,
	@intBillDetail_InProdDetailId		INT,
	@intillDetail_InProdDetailAmount	INT,
	@intBillDetailId					INT,
	@intAmountSaled						INT,
	@intAmountOut						INT,
	@intAmountChange					INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/


/*_______________________________ MAIN _________________________________*/

	BEGIN TRAN UpdateProdDetail
	BEGIN TRY		
		SELECT @oldAmount = InOutProdDetail.Amount,
			@oldRemainAmount = InOutProdDetail.RemainAmount
		FROM InOutProdDetail
		WHERE Id = @inPaperDetailId
	
		UPDATE InOutProdDetail
		SET Amount = @amount , InputPrice = @inputPrice
		WHERE Id = @inPaperDetailId
		
		SET @intAmountChange = @amount - @oldAmount
		
		IF @oldAmount <> @amount BEGIN
			UPDATE Product
			SET Amount = Amount + @intAmountChange
			WHERE Id = @productId
			
			-- Sum amount of bill detail saled from this input detail
			SET @intAmountSaled = (SELECT SUM(Amount)
									FROM BillDetail_InProdDetail
									WHERE BillDetail_InProdDetail.InProdDetailId = @inPaperDetailId)
									
			SET @intAmountOut = (@oldAmount - @oldRemainAmount) - ISNULL(@intAmountSaled, 0)
			
			SET @amount = @amount - @intAmountOut -- amount for sale
			UPDATE InOutProdDetail
			SET RemainAmount = CASE
								WHEN @amount > 0 THEN @amount
								ELSE 0
							   END
			WHERE Id = @inPaperDetailId		
			
			-- Set null and re-update BillDetail_InProdDetail
			-- UPDATE BillDetail_InProdDetail, set null for related BillDetail_InProdDetail
			UPDATE BillDetail_InProdDetail
			SET InProdDetailId = NULL
			WHERE InProdDetailId = @inPaperDetailId			
			
			-- reupdate
			-- Update BillDetail_InProDetail table
			WHILE @amount > 0 BEGIN
				SELECT TOP 1
					@intBillDetail_InProdDetailId = BillDetail_InProdDetail.Id,
					@intillDetail_InProdDetailAmount = BillDetail_InProdDetail.Amount,
					@intBillDetailId = BillDetail_InProdDetail.BillDetailId
				FROM BillDetail_InProdDetail
				
				INNER JOIN BillDetail
					ON BillDetail_InProdDetail.BillDetailId = BillDetail.Id
				
				WHERE BillDetail_InProdDetail.InProdDetailId IS NULL
					AND BillDetail.ProductId = @productId		
				ORDER BY BillDetail_InProdDetail.BillDetailId
				
				IF @intBillDetail_InProdDetailId IS NOT NULL BEGIN
					-- Update amount of BillDetail_InProdDetail
					UPDATE BillDetail_InProdDetail
					SET InProdDetailId = @inPaperDetailId,
						Amount = CASE
									WHEN Amount > @amount THEN @amount
									ELSE Amount
								 END
					WHERE Id = @intBillDetail_InProdDetailId
					
					IF @intillDetail_InProdDetailAmount > @amount BEGIN
						-- If input amount has not full-fill amount of BillDetail_InProdDetail
						-- Inser new BillDetail_InProdDetail with remain amount
						INSERT INTO BillDetail_InProdDetail (
							BillDetailId,
							InProdDetailId,
							Amount
						) VALUES (
							@intBillDetailId,
							NULL,
							@intillDetail_InProdDetailAmount - @amount
						)
					END
					
					SET @amount = @amount - @intillDetail_InProdDetailAmount
					IF @amount <= 0 BEGIN
						-- Update remain amount of input
						UPDATE InOutProdDetail
						SET RemainAmount = 0
						WHERE Id = @inPaperDetailId
					END
					
					SET @intBillDetail_InProdDetailId = NULL
					SET @intillDetail_InProdDetailAmount = 0
				END
				ELSE BEGIN
					-- Update remain amount of input
					UPDATE InOutProdDetail
					SET RemainAmount = @amount
					WHERE Id = @inPaperDetailId
					
					SET @amount = 0 -- break while
				END
			END -- End While
		END -- end compare old amount vs new amount
		
		-- Fill BillDetail_InProd detail by new In_Prod Detail (replace the delete one by new one)
		-- Just need do this when amount change < 0, because if change > 0, the BillDetail_InProDetail
		-- already filled by above code
		IF @intAmountChange < 0 BEGIN
			EXEC pUpdateBillDetail_InProdDetail_AfterInDetailChange
				@vintProductId = @productId				
		END
		
		SET @intReturn = 1
		COMMIT TRANSACTION UpdateProdDetail
	END TRY
	BEGIN CATCH
	  ROLLBACK TRANSACTION UpdateProdDetail
	END CATCH
		
	SELECT @intReturn 



